package DBLayer;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import ModelLayer.Customer;
import ModelLayer.Employee;



public class DBCustomer {

    private Connection con;

    // Constructor
    public DBCustomer(){
        con = DbConnection1.getInstance().getDBcon();
    }
    
    
    public ArrayList<Customer> getAllCustomers()
    {
        return miscWhere("");
    }
    
    public int insertCustomer(Customer cus) throws Exception
    {  
        int nextIdCustomer = GetMax.getMaxId("Select max(idCustomer) from customer");
        nextIdCustomer = nextIdCustomer + 1;
        System.out.println("next idCustomer = " +  nextIdCustomer);
  
       int rc = -1;
	   String query="INSERT INTO customer(idCustomer ,name, address, zipCode, city, phoneNo, email, cvrNo, AccountNo)  VALUES('"+
			nextIdCustomer  + "','"  +
			cus.getName()  + "','"  +
		    cus.getAddress()  + "','"  +
			cus.getZipCode() + "','" +
		    cus.getCity()  + "','"  +
		    cus.getPhoneNo()  + "','"  +
		    cus.getEmail()   + "','"  +
		    cus.getCvrNo()  + "','"  +
		    cus.getAccountNo()  + "')";
            
                    

       System.out.println("insert : " + query);
       try{ // insert new Customer +  dependent
          Statement stmt = con.createStatement();
          stmt.setQueryTimeout(5);
     	  rc = stmt.executeUpdate(query);
          stmt.close();
       }//end try
       catch(SQLException ex){
          System.out.println("Customer not created");
          throw new Exception ("Customer is not inserted correct");
       }
       return(rc);
    }
    
    public Customer findCustomerByName(String name)
    {   String wClause = "  name = '" + name + "'";
        return singleWhere(wClause);
    }
    
    public Customer findCustomerById(String idCustomer)
    {   String wClause = "  idCustomer = '" + idCustomer + "'";
        return singleWhere(wClause);
    }
    
    public int updateCustomer(Customer cus)
	{
		Customer cusObj  = cus;
		int rc=-1;

		String query="UPDATE employee SET "+
			"name ='"+ cusObj.getName() + "', " +
            "address ='"+ cusObj.getAddress() + "', " +
			"zipCode ='"+ cusObj.getZipCode() +"', " +
            "city ='"+ cusObj.getCity() + "', " +
            "phoneNo ='"+ cusObj.getPhoneNo() + "', " +
            "email ='"+ cusObj.getEmail() + "' " +	
		 	"cvr ='"+ cusObj.getCvrNo()+"', "+
			"AccountNo ='"+ cusObj.getAccountNo() + "', " +
            " WHERE idCustomer = '"+ cusObj.getIdCustomer() + "'";
			System.out.println("Update query:" + query);
			
  		try{
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	 	rc = stmt.executeUpdate(query);

	 	 	stmt.close();
		}
	 	catch(Exception ex){
	 	 	System.out.println("Update exception in customer db: "+ex);
	  	}
		return(rc);
	}
    
    public int deleteCustomer(String idCustomer)
	{
        int rc=-1;
	  
	  	String query="DELETE FROM customer WHERE idCustomer = '" + idCustomer + "'";
        System.out.println(query);
	  	
        try{
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	  	rc = stmt.executeUpdate(query);
	 	  	stmt.close();
  		}//slut try	
        catch(Exception ex)
        {
	 	  	System.out.println("Delete exception in Customer db: "+ex);
        }
		return(rc);
	}
    
    /* singleWhere, miscWhere, queries*/
    private ArrayList<Customer> miscWhere(String wClause)
	{
        ResultSet results;
	    ArrayList<Customer> list = new ArrayList<Customer>();	
		
	    String query =  buildQuery(wClause);
  
        try{ // read the Customer from the database
			Statement stmt = con.createStatement();
		 	stmt.setQueryTimeout(5);
		 	results = stmt.executeQuery(query);
		 	
		
			while( results.next() )
			{
		     	 Customer cusObj = new Customer();
		     	 cusObj = buildCustomer(results);	
                 list.add(cusObj);	
			}
	        stmt.close();   
	        //                 if(retrieveAssociation)
	//                 {   //The supervisor and department is to be build as well
	//                     for(Customer cusObj : list){
	//                        String superssn = cusObj.getSupervisor().getSsn();
	//                        Customer supercus = singleWhere(" ssn = '" + superssn + "'",false);
	//                        cusObj.setSupervisor(supercus);
	//                        System.out.println("Supervisor is seleceted");
	//                       // here the department has to be selected as well
	//                     }
	//                 }//end if   
				
		}
        catch(Exception e)
    	{
    		System.out.println("Query exception - select: "+e);
    		e.printStackTrace();
    	}
        return list;
	 	
	}
    
    private Customer singleWhere(String wClause)
	{
		ResultSet results;
		Customer cusObj = new Customer();
                
	    String query =  buildQuery(wClause);
        System.out.println(query);
		try{ // read the Customer from the database
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 		
	 		if( results.next() )
	 		{
                            cusObj = buildCustomer(results);
                            //assocaition is to be build
                            stmt.close();
//                            if(retrieveAssociation)
//                            {   //The supervisor and department is to be build as well
//                                String superssn = empObj.getSupervisor().getSsn();
//                                Customer superEmp = singleWhere(" ssn = '" + superssn + "'",false);
//                                empObj.setSupervisor(superEmp);
//                                System.out.println("Supervisor is seleceted");
//                                System.out.println("Department is selected");
//                                DBDepartment dbObj = new DBDepartment();
//                                empObj.setDepartment(dbObj.findDepartment(empObj.getDept().getDnumber(), false));
//                                System.out.println("Department is selected");
//                           
//                           
//                            }
	 		}
            else
            	cusObj = null;
            
		}//end try	
	 	catch(Exception e){
	 		System.out.println("Query exception: "+e);
	 	}
		return cusObj;
	}
    
    private String buildQuery(String wClause)
	{
	    String query="SELECT idCustomer, name, address, city, phoneNo, email, cvrNo, accountNo FROM customer";
		
		if (wClause.length()>0)
			query=query+" WHERE "+ wClause;
			
		return query;
	}
    
    private Customer buildCustomer(ResultSet results)
    {   
    	Customer cusObj = new Customer();
        try
        { // the columns from the table Customer  are used
    		cusObj.setIdCustomer(results.getString("idCustomer"));
    		cusObj.setName(results.getString("name"));
			cusObj.setAddress(results.getString("address"));
			cusObj.setCity(results.getString("city"));
			cusObj.setPhoneNo(results.getString("phoneNo"));
			cusObj.setEmail(results.getString("email"));
			cusObj.setCvrNo(results.getString("cvrNo"));
    		cusObj.setAccountNo(results.getString("accountNo"));
        }
       catch(Exception e)
       {
           System.out.println("error in building the Customer object");
       }
       return cusObj;
    }

}
